﻿using Ayatta.Contract.Component;
using Ayatta.Service.Extension;
using Dapper;
using System;
using System.Linq;
using Ayatta.Contract;
using Ayatta.Contract.Domain;
using System.Collections.Generic;

namespace Ayatta.Service
{
    public class AuctionService : ServiceBase, IAuctionService
    {
        #region Auction

        public bool CreateAuction(Auction o)
        {
            var builder = new SqlBuilder()
            .Table(Database.Auction.Table.Auction)
            .Field("([Id],[SkuId],[ItemId],[ProductId],[IsVirtual],[Grade],[PerPrice],[Interval],[Name],[PictureSrc],[Description],[Cost],[BuyPrice],[MarketPrice],[SafePrice],[IntegralExchange],[DiffBuySupported],[DiffBuyLimitation],[DiffBuyLimitationValue],[ZeroSupported],[FreeBidSupported],[FreeBidLimitation],[FreeBidLimitationValue],[AdditionalCharge],[OnlineTime],[StartTime],[StopTime],[SellerId],[UserId],[NickName],[UserName],[BidPrice],[ExpectedTime],[TransactionTime],[IpAddress],[OrderId],[Extra],[Status],[CreatedBy],[CreatedOn])VALUES(@Id,@SkuId,@ItemId,@ProductId,@IsVirtual,@Grade,@PerPrice,@Interval,@Name,@PictureSrc,@Description,@Cost,@BuyPrice,@MarketPrice,@SafePrice,@IntegralExchange,@DiffBuySupported,@DiffBuyLimitation,@DiffBuyLimitationValue,@ZeroSupported,@FreeBidSupported,@FreeBidLimitation,@FreeBidLimitationValue,@AdditionalCharge,@OnlineTime,@StartTime,@StopTime,@SellerId,@UserId,@NickName,@UserName,@BidPrice,@ExpectedTime,@TransactionTime,@IpAddress,@OrderId,@Extra,@Status,@CreatedBy,@CreatedOn)")
            .AddParameters(new { o.Id, o.SkuId, o.ItemId, o.ProductId, o.IsVirtual, o.Grade, o.PerPrice, o.Interval, o.Name, o.PictureSrc, o.Description, o.Cost, o.BuyPrice, o.MarketPrice, o.SafePrice, o.IntegralExchange, o.DiffBuySupported, o.DiffBuyLimitation, o.DiffBuyLimitationValue, o.ZeroSupported, o.FreeBidSupported, o.FreeBidLimitation, o.FreeBidLimitationValue, o.AdditionalCharge, o.OnlineTime, o.StartTime, o.StopTime, o.SellerId, o.UserId, o.NickName, o.UserName, o.BidPrice, o.ExpectedTime, o.TransactionTime, o.IpAddress, o.OrderId, o.Extra, o.Status, o.CreatedBy, o.CreatedOn });

            var template = builder.AddTemplate(SqlTemplate.Insert);
            using (var connection = GetConnection(ConnectionName.Auction))
            {
                return Execute(connection, template) == 1;
            }
        }

        public bool UpdateAuction(Auction o)
        {
            var builder = new SqlBuilder()
                .Table(Database.Auction.Table.Auction)
                .Field("[Grade]=@Grade", new { o.Grade })
                .Field("[PerPrice]=@PerPrice", new { o.PerPrice })
                .Field("[Interval]=@Interval", new { o.Interval })
                .Field("[Name]=@Name", new { o.Name })
                .Field("[PictureSrc]=@PictureSrc", new { o.PictureSrc })
                .Field("[Description]=@Description", new { o.Description })
                .Field("[Cost]=@Cost", new { o.Cost })
                .Field("[BuyPrice]=@BuyPrice", new { o.BuyPrice })
                .Field("[MarketPrice]=@MarketPrice", new { o.MarketPrice })
                .Field("[SafePrice]=@SafePrice", new { o.SafePrice })
                .Field("[IntegralExchange]=@IntegralExchange", new { o.IntegralExchange })
                .Field("[DiffBuySupported]=@DiffBuySupported", new { o.DiffBuySupported })
                .Field("[DiffBuyLimitation]=@DiffBuyLimitation", new { o.DiffBuyLimitation })
                .Field("[DiffBuyLimitationValue]=@DiffBuyLimitationValue", new { o.DiffBuyLimitationValue })
                .Field("[ZeroSupported]=@ZeroSupported", new { o.ZeroSupported })
                .Field("[FreeBidSupported]=@FreeBidSupported", new { o.FreeBidSupported })
                .Field("[FreeBidLimitation]=@FreeBidLimitation", new { o.FreeBidLimitation })
                .Field("[FreeBidLimitationValue]=@FreeBidLimitationValue", new { o.FreeBidLimitationValue })
                .Field("[AdditionalCharge]=@AdditionalCharge", new { o.AdditionalCharge })
                .Field("[OnlineTime]=@OnlineTime", new { o.OnlineTime })
                .Field("[StartTime]=@StartTime", new { o.StartTime })
                .Field("[StopTime]=@StopTime", new { o.StopTime })
                .Field("[ExpectedTime]=@ExpectedTime", new { o.ExpectedTime })
                .Field("[TransactionTime]=@TransactionTime", new { o.TransactionTime })
                .Field("[IpAddress]=@IpAddress", new { o.IpAddress })
                .Field("[OrderId]=@OrderId", new { o.OrderId })
                .Field("[Extra]=@Extra", new { o.Extra })
                .Field("[Status]=@Status", new { o.Status })
                .Where("[Id] = @Id", new { o.Id });

            var template = builder.AddTemplate(SqlTemplate.Update);
            using (var connection = GetConnection(ConnectionName.Auction))
            {
                return Execute(connection, template) == 1;
            }
        }

        public bool DeleteAuction(long id)
        {
            var builder = new SqlBuilder()
                .Table(Database.Auction.Table.Auction)
                .Where("[Id] = @Id", new { id });

            var template = builder.AddTemplate(SqlTemplate.Delete);
            using (var connection = GetConnection(ConnectionName.Auction))
            {
                return Execute(connection, template) == 1;
            }
        }

        public string Buy(long id)
        {
            return string.Empty;
        }

        public int SumBid(long userId, long auctionId, DateTime? begin = null)
        {
            var builder = new SqlBuilder()
                .Field("ISNULL(SUM([Price]),0) as [Sum]")
                .Table(Database.Auction.Table.Bidding)
                .Where("[UserId] = @UserId", new { userId })
                .Where("[AuctionId] = @AuctionId", new { auctionId });
            if (begin.HasValue)
            {
                builder.Where("[CreatedOn]>=@CreatedOn", new { createdOn = begin });
            }

            var template = builder.AddTemplate(SqlTemplate.SelectTopOne);
            using (var connection = GetConnection(ConnectionName.Auction))
            {
                return Query<int>(connection, template).FirstOrDefault();
            }
        }

        public Auction GetAuction(long id)
        {
            var builder = new SqlBuilder()
                .Field("*")
                .Table(Database.Auction.Table.Auction)
                .Where("[Id]=@Id", new { id });

            var template = builder.AddTemplate(SqlTemplate.Select);
            using (var connection = GetConnection(ConnectionName.Auction))
            {
                return Query<Auction>(connection, template).FirstOrDefault();
            }
        }

        public IList<Auction> GetAllAuction()
        {
            var builder = new SqlBuilder()
                .Field("*")
                .Table(Database.Auction.Table.Auction);

            var template = builder.AddTemplate(SqlTemplate.Select);
            using (var connection = GetConnection(ConnectionName.Auction))
            {
                return Query<Auction>(connection, template).ToList();
            }
        }

        public PagedList<Auction> GetPagedAuctions(int index = 1, int size = 20, string name = null, AuctionGrade? grade = null, AuctionStatus? status = null)
        {
            var builder = new SqlBuilder().Field("*")
                .Table(Database.Auction.Table.Auction)
                .OrderBy("id desc")
                .AddParameters(new { begin = (index - 1) * size + 1, end = index * size });
            if (!string.IsNullOrEmpty(name))
            {
                builder.Where("[Name] LIKE @Name", new { name = name + "%" });
            }
            if (grade.HasValue)
            {
                builder.Where("[Grade]=@Grade", new { grade });
            }
            if (status.HasValue)
            {
                builder.Where("[Status]=@Status", new { status });
            }

            var template = builder.AddTemplate(SqlTemplate.PagedSelect);
            using (var connection = GetConnection(ConnectionName.Auction).EnsureOpen())
            {
                return connection.PagedQuery<Auction>(template);
            }
        }

        #endregion

        #region BiddingRecord

        public PagedList<Bid> GetPagedBidding(int index = 1, int size = 20, long? auctionId = null)
        {
            var builder = new SqlBuilder().Field("*")
                .Table(Database.Auction.Table.Bidding)
                .OrderBy("id desc")
                .AddParameters(new { begin = (index - 1) * size + 1, end = index * size });
            /*
            .Where("");
            */

            var template = builder.AddTemplate(SqlTemplate.PagedSelect);
            using (var connection = GetConnection(ConnectionName.Auction).EnsureOpen())
            {
                return connection.PagedQuery<Bid>(template);
            }
        }

        #endregion

        #region Auction.Order

        public bool CreateAuctionOrder(AuctionOrder o)
        {
            var builder = new SqlBuilder()
                .Table(Database.Auction.Table.AuctionOrder)
                .Field(
                    "([UserId],[Category],[AuctionId],[Grade],[Name],[PictureSrc],[Description],[BidPrice],[BuyPrice],[MarketPrice],[Quantity],[Deduction],[AdditionalCharge],[Total],[IntegralExchange],[ShippingMethod],[PaymentMethod],[PaidDate],[ShippingDate],[DeliveryDate],[OrderStatus],[ShippingStatus],[PaymentStatus],[Expiration],[AreaCode],[Receiver],[Phone],[Mobile],[Province],[City],[Area],[Address],[PostalCode],[InvoiceCategory],[InvoiceTitle],[InvoiceContent],[IpAddress],[Mesage],[Extra],[CreatedBy],[CreatedOn])VALUES(@UserId,@Category,@AuctionId,@Grade,@Name,@PictureSrc,@Description,@BidPrice,@BuyPrice,@MarketPrice,@Quantity,@Deduction,@AdditionalCharge,@Total,@IntegralExchange,@ShippingMethod,@PaymentMethod,@PaidDate,@ShippingDate,@DeliveryDate,@OrderStatus,@ShippingStatus,@PaymentStatus,@Expiration,@AreaCode,@Receiver,@Phone,@Mobile,@Province,@City,@Area,@Address,@PostalCode,@InvoiceCategory,@InvoiceTitle,@InvoiceContent,@IpAddress,@Mesage,@Extra,@CreatedBy,@CreatedOn)")
                .AddParameters(
                    new {
                        o.UserId,
                        o.Category,
                        o.AuctionId,
                        o.Grade,
                        o.Name,
                        o.PictureSrc,
                        o.Description,
                        o.BidPrice,
                        o.BuyPrice,
                        o.MarketPrice,
                        o.Quantity,
                        o.Deduction,
                        o.AdditionalCharge,
                        o.Total,
                        o.IntegralExchange,
                        o.ShippingMethod,
                        o.PaymentMethod,
                        o.PaidDate,
                        o.ShippingDate,
                        o.DeliveryDate,
                        o.OrderStatus,
                        o.ShippingStatus,
                        o.PaymentStatus,
                        o.Expiration,
                        o.AreaCode,
                        o.Receiver,
                        o.Phone,
                        o.Mobile,
                        o.Province,
                        o.City,
                        o.Area,
                        o.Address,
                        o.PostalCode,
                        o.InvoiceCategory,
                        o.InvoiceTitle,
                        o.InvoiceContent,
                        o.IpAddress,
                        o.Mesage,
                        o.Extra,
                        o.CreatedBy,
                        o.CreatedOn
                    });
            var template = builder.AddTemplate(SqlTemplate.Insert);
            using (var connection = GetConnection(ConnectionName.Auction))
            {
                return Execute(connection, template) == 1;
            }
        }

        public bool UpdateAuctionOrder(AuctionOrder o)
        {
            var builder = new SqlBuilder()
                .Table(Database.Auction.Table.AuctionOrder)
                .Field("[BidPrice]=@BidPrice", new { o.BidPrice })
                .Field("[BuyPrice]=@BuyPrice", new { o.BuyPrice })
                .Field("[MarketPrice]=@MarketPrice", new { o.MarketPrice })
                .Field("[Quantity]=@Quantity", new { o.Quantity })
                .Field("[Deduction]=@Deduction", new { o.Deduction })
                .Field("[AdditionalCharge]=@AdditionalCharge", new { o.AdditionalCharge })
                .Field("[Total]=@Total", new { o.Total })
                .Field("[IntegralExchange]=@IntegralExchange", new { o.IntegralExchange })
                .Field("[ShippingMethod]=@ShippingMethod", new { o.ShippingMethod })
                .Field("[PaymentMethod]=@PaymentMethod", new { o.PaymentMethod })
                .Field("[PaidDate]=@PaidDate", new { o.PaidDate })
                .Field("[ShippingDate]=@ShippingDate", new { o.ShippingDate })
                .Field("[DeliveryDate]=@DeliveryDate", new { o.DeliveryDate })
                .Field("[OrderStatus]=@OrderStatus", new { o.OrderStatus })
                .Field("[ShippingStatus]=@ShippingStatus", new { o.ShippingStatus })
                .Field("[PaymentStatus]=@PaymentStatus", new { o.PaymentStatus })
                .Field("[Expiration]=@Expiration", new { o.Expiration })
                .Field("[AreaCode]=@AreaCode", new { o.AreaCode })
                .Field("[Receiver]=@Receiver", new { o.Receiver })
                .Field("[Phone]=@Phone", new { o.Phone })
                .Field("[Mobile]=@Mobile", new { o.Mobile })
                .Field("[Province]=@Province", new { o.Province })
                .Field("[City]=@City", new { o.City })
                .Field("[Area]=@Area", new { o.Area })
                .Field("[Address]=@Address", new { o.Address })
                .Field("[PostalCode]=@PostalCode", new { o.PostalCode })
                .Field("[InvoiceCategory]=@InvoiceCategory", new { o.InvoiceCategory })
                .Field("[InvoiceTitle]=@InvoiceTitle", new { o.InvoiceTitle })
                .Field("[InvoiceContent]=@InvoiceContent", new { o.InvoiceContent })
                .Field("[IpAddress]=@IpAddress", new { o.IpAddress })
                .Field("[Mesage]=@Mesage", new { o.Mesage })
                .Field("[Extra]=@Extra", new { o.Extra })
                .Where("[Id] = @Id And [UserId]=UserId", new { o.Id, o.UserId });

            var template = builder.AddTemplate(SqlTemplate.Update);
            using (var connection = GetConnection(ConnectionName.Auction))
            {
                return Execute(connection, template) == 1;
            }
        }

        public bool DeleteAuctionOrder(string id)
        {
            var builder = new SqlBuilder()
                .Table(Database.Auction.Table.AuctionOrder)
                .Where("[Id] = @Id", new { id });

            var template = builder.AddTemplate(SqlTemplate.Delete);
            using (var connection = GetConnection(ConnectionName.Auction))
            {
                return Execute(connection, template) == 1;
            }
        }


        public AuctionOrder GetAuctionOrder(string id, int? userId = null)
        {
            var builder = new SqlBuilder()
                .Field("*")
                .Table(Database.Auction.Table.AuctionOrder)
                .Where("[Id] = @Id", new { id });

            if (userId.HasValue && userId.Value > 0)
            {
                builder.Where("[UserId] = @UserId", new { userId });
            }
            var template = builder.AddTemplate(SqlTemplate.SelectTopOne);
            using (var connection = GetConnection(ConnectionName.Auction))
            {
                return Query<AuctionOrder>(connection, template).FirstOrDefault();
            }
        }

        public IList<AuctionOrder> GetAllAuctionOrder()
        {
            throw new NotImplementedException();
        }

        public PagedList<AuctionOrder> GetPagedAuctionOrder(int index = 1, int size = 20, int? userId = 0, AuctionGrade? grade = null, AuctionStatus? status = null)
        {
            var builder = new SqlBuilder().Field("*")
                .Table(Database.Auction.Table.AuctionOrder)
                .OrderBy("id desc")
                .AddParameters(new { begin = (index - 1) * size + 1, end = index * size });

            if (userId.HasValue && userId.Value > 0)
            {
                builder.Where("[UserId]=@UserId", new { userId });
            }
            if (grade.HasValue && grade.Value > 0)
            {
                builder.Where("[Grade]=@Grade", new { grade });
            }
            if (status.HasValue && status.Value > 0)
            {
                builder.Where("[Status]=@Status", new { status });
            }

            var template = builder.AddTemplate(SqlTemplate.PagedSelect);
            using (var connection = GetConnection(ConnectionName.Auction).EnsureOpen())
            {
                return connection.PagedQuery<AuctionOrder>(template);
            }
        }
        #endregion
        #region AuctionShare
        public bool CreateAuctionShare(AuctionShare o)
        {
            throw new NotImplementedException();
        }

        public AuctionShare GetAuctionShare(int id, int? userId = null, AuctionShareStatus? status = null)
        {
            var builder = new SqlBuilder().Field("*")
                .Table(Database.Auction.Table.AuctionShare)
                .Where("[Id]=@Id", new { id });
            if (userId.HasValue && userId.Value > 0)
            {
                builder.Where("[UserId]=@UserId", new { userId });
            }
            if (status.HasValue)
            {
                builder.Where("[Status]=@Status", new { status });
            }
            var template = builder.AddTemplate(SqlTemplate.Select);
            using (var connection = GetConnection(ConnectionName.Auction))
            {
                return Query<AuctionShare>(connection, template).FirstOrDefault();
            }
        }

        public IList<AuctionShare> GetAuctionShares(int userId)
        {
            var builder = new SqlBuilder().Field("*")
                .Table(Database.Auction.Table.AuctionShare)
                .Where("[UserId]=@UserId", new { userId });

            var template = builder.AddTemplate(SqlTemplate.Select);
            using (var connection = GetConnection(ConnectionName.Auction))
            {
                return Query<AuctionShare>(connection, template).ToList();
            }
        }

        public PagedList<AuctionShare> GetPagedAuctionShares(int index = 1, int size = 20, int? userId = null, string username = null, int? auctionId = null, AuctionShareStatus? status = null)
        {
            var builder = new SqlBuilder().Field("*")
                .Table(Database.Auction.Table.AuctionShare)
                .OrderBy("Id desc")
                .AddParameters(new { begin = (index - 1) * size + 1, end = index * size });

            if (userId.HasValue && userId.Value > 0)
            {
                builder.Where("[UserId]=@UserId", new { userId });
            }
            if (!string.IsNullOrEmpty(username))
            {
                builder.Where("[CreatedBy]=@username", new { username });
            }
            if (auctionId.HasValue && auctionId.Value > 0)
            {
                builder.Where("[AuctionId]=@AuctionId", new { auctionId });
            }
            if (status.HasValue)
            {
                builder.Where("[Status]=@Status", new { status });
            }

            var template = builder.AddTemplate(SqlTemplate.PagedSelect);
            using (var connection = GetConnection(ConnectionName.Auction).EnsureOpen())
            {
                return connection.PagedQuery<AuctionShare>(template);
            }
        }
        #endregion
        #region AuctionRobot
        public bool CreateAuctionRobot(AuctionRobot o)
        {
            throw new NotImplementedException();
        }

        public AuctionRobot GetAuctionRobot(int id, int? userId = null, AuctionRobotStatus? status =null)
        {
            var builder = new SqlBuilder().Field("*")
               .Table(Database.Auction.Table.AuctionRobot)
               .Where("[Id]=@Id", new { id });
            if (userId.HasValue && userId.Value > 0)
            {
                builder.Where("[UserId]=@UserId", new { userId });
            }
            if (status.HasValue)
            {
                builder.Where("[Status]=@Status", new { status });
            }
            var template = builder.AddTemplate(SqlTemplate.Select);
            using (var connection = GetConnection(ConnectionName.Auction))
            {
                return Query<AuctionRobot>(connection, template).FirstOrDefault();
            }
        }

        public IList<AuctionRobot> GetAuctionRobots(int userId)
        {
            var builder = new SqlBuilder().Field("*")
                .Table(Database.Auction.Table.AuctionRobot)
                .Where("[UserId]=@UserId", new { userId });

            var template = builder.AddTemplate(SqlTemplate.Select);
            using (var connection = GetConnection(ConnectionName.Auction))
            {
                return Query<AuctionRobot>(connection, template).ToList();
            }
        }

        public PagedList<AuctionRobot> GetPagedAuctionRobots(int index = 1, int size = 20, int? userId = null, string username = null, int? auctionId = null, AuctionRobotCategory? category = null, AuctionRobotTiming? timing = null, AuctionRobotStatus? status = null)
        {
            var builder = new SqlBuilder().Field("*")
                .Table(Database.Auction.Table.AuctionRobot)
                .OrderBy("Id desc")
                .AddParameters(new {begin = (index - 1)*size + 1, end = index*size});

            if (userId.HasValue && userId.Value > 0)
            {
                builder.Where("[UserId]=@UserId", new {userId});
            }
            if (!string.IsNullOrEmpty(username))
            {
                builder.Where("[CreatedBy]=@username", new {username});
            }
            if (auctionId.HasValue && auctionId.Value > 0)
            {
                builder.Where("[AuctionId]=@AuctionId", new {auctionId});
            }
            if (category.HasValue)
            {
                builder.Where("[Category]=@Category", new {category});
            }
            if (timing.HasValue)
            {
                builder.Where("[Timing]=@Timing", new {timing});
            }
            if (status.HasValue)
            {
                builder.Where("[Status]=@Status", new {status});
            }
            var template = builder.AddTemplate(SqlTemplate.PagedSelect);
            using (var connection = GetConnection(ConnectionName.Auction).EnsureOpen())
            {
                return connection.PagedQuery<AuctionRobot>(template);
            }
        }

        #endregion

        #region Implementation of IService

        public int GetCachedSqlCount()
        {
            return SqlMapper.GetCachedSQLCount();
        }
        public List<Tuple<string, string, int>> GetCachedSql()
        {
            return SqlMapper.GetCachedSQL().ToList();
        }
        public void PurgeQueryCache()
        {
            SqlMapper.PurgeQueryCache();
        }
        #endregion

    }
}